<?php
namespace app\sign\controller;
use app\common\Base;
use app\common\Excel;
use think\Db;

class WechatSign extends Base {
	
	public function index() {
		return $this->fetch();
	}

	public function pageQuery() {
		$page = input("page");
		$rows = input("rows");
		$sql = $this::getSql();
		$result = Db::table("(".$sql.") sub")
				  ->page($page, $rows)
				  ->order("日期,教师ID,签到二维码ID")
				  ->select();
		$count = Db::table("(".$sql.") sub")->count();
		$grid['rows'] = selectResult($result);
        $grid['total'] = $count;
		return $grid;
	}

	public function exportExcel() {
		$page = input("page");
		$rows = input("rows");
		$sql = $this::getSql();
		$result = Db::table("(".$sql.") sub")
				  ->order("日期,教师ID,签到二维码ID")
				  ->select();
		$header = array('日期'=>'日期','教师姓名'=>'教师姓名','签到主题'=>'签到主题','签到状态'=>'签到状态','签到时间'=>'签到时间');
		Excel::toExcel(selectResult($result),'签到记录',$header);
		exit;
	}

	public function signType() {
		return Db::table("签到二维码")
					->field("ID value, 签到主题 text")
					->select();
	}

	public function getSql() {
		$teacherName = input("teacherName");
		$signType = input("signType");
		$signStatus = input("signStatus");
		$teacherType = input("teacherType");
		$teacherTypeArray = null;
		$grade = null;
		$studyType = null;
		if($teacherType) {
			$teacherTypeArray = explode("_", $teacherType);
			if(count($teacherTypeArray) > 1) {
				$grade = $teacherTypeArray[0];
				$studyType = $teacherTypeArray[1];
			}
		}
		$startDate = input("startDate");
		$endDate = input("endDate");
		if(!$startDate) $startDate = date('Y-m-d');
		if(!$endDate) $endDate = date('Y-m-d');
		$diff = date_diff(date_create($startDate), date_create($endDate));
		$day = $diff->format("%a");
		$dateSql = "SELECT '".$startDate."' 日期";
		for ($x=0; $x<$day; $x++) {
		  $startDate = date('Y-m-d',strtotime("$startDate +1 day"));
		  $dateSql .= " UNION SELECT '".$startDate."'";
		} 
		$sql = "SELECT a.ID 教师ID,姓名 教师姓名,ab.签到二维码ID,签到主题,日期, 签到时间,补签到,备注,case when 签到时间 is null then '未签' else '已签' end as 签到状态
		FROM 教师信息 a 
		LEFT JOIN ( 
		  SELECT ID 签到二维码ID, 签到主题, 日期
		  FROM 签到二维码 a
		  INNER JOIN (".$dateSql.") c ON 1 = 1
		) ab ON 1=1 
		LEFT JOIN 签到记录 b ON a.ID = b.教师ID AND 
			ab.签到二维码ID = b.签到二维码ID AND CONVERT(varchar(100), b.签到时间, 23) = ab.日期 "." WHERE 离校学期ID = 0 and a.签到 = 1".($teacherName ? "and a.姓名='".$teacherName."'" : "").(!$signType ? " and ab.签到二维码ID in (1,2)" : " and ab.签到二维码ID = ".$signType).($signStatus ? ($signStatus == 1 ? " and 签到时间 is not null" : " and 签到时间 is null") : "").($grade ? " and a.年级号 = ".$grade : "").($studyType ? " and a.学籍类型 = ".$studyType : "").
			($teacherTypeArray ? (count($teacherTypeArray) == 1 ? " and exists(select 1 from T_ORGANIZE_GROUP og where GROUP_CODE = '".$teacherType."' and dbo.is_mixed(og.IDS,a.职位) = 1)" : " and not exists(select 1 from T_ORGANIZE_GROUP og where GROUP_CODE in ('MANAGE-GROUP','GZZY-GROUP') and dbo.is_mixed(og.IDS,a.职位) = 1)") : "")
			;
		return $sql;
	}

}
